package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.common.util.DateUtil;
import net.tycmc.bulb.common.util.MapGetter;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.DateControl;
import net.tycmc.cty.ctyms.timejob.common.DateList;
import net.tycmc.cty.ctyms.timejob.common.EquipVersionCommon;
import net.tycmc.cty.ctyms.timejob.common.SMSCfgType;
import net.tycmc.cty.ctyms.timejob.common.SMSPromptType;

import com.tycmc.functions.ExtTrackPoint;
import com.tycmc.functions.util.StringUtil;

/**
 * 门控报警
 * @author lixiaofan
 */
public class GatingAlarmDAO {
	IBaseDAO baseDAO;
	private SMSInfoDAO smsInfoDAO;
	private ITransactionDAO tranDAO;
	
	public List<Map<String,Object>> getSourceData(){
		//获取上一次统计到的ID	1:门控报警
		Map<String, Object> StatRecord = getStatDataID("1");
		String TableName;
		String TableName_New = null;
		boolean isTwo = false;
		if(StatRecord!=null && StatRecord.size()>0){
			String StatR_TableData = MapGetter.getString(StatRecord, "StatR_TableData");	//上次统计的表日期
			if(DateControl.getGapDays(StatR_TableData)>0){	//StatR_TableData小于当然日期
				TableName = DateUtil.addDay(-1).replace("-", "");
				TableName_New = DateUtil.addDay(0).replace("-", "");
				isTwo = true;
			}else if(DateControl.getGapDays(StatR_TableData)==0){	//StatR_TableData等于当前日期
				TableName = DateUtil.addDay(0).replace("-", "");
			}else{	//StatR_TableData大于当前日期
				return null;
			}
		}else{	//初次统计，统计今天的数据
			TableName = DateUtil.addDay(0).replace("-", "");
		}
		
		//判断小一数据库表是否存在
		String sql_B = "select count(1) as counts from CTY_Egn_B.dbo.SysObjects Where XType='U' and Name=?";
		if(isTwo){
			sql_B += " or Name='"+"Msg_Timing_"+TableName_New+"' ";
		}
		Map<String,Object> map_B = baseDAO.execQueryOne(sql_B, new Object[]{"Msg_Timing_"+TableName});
		int counts_B = MapGetter.getInt(map_B, "counts");
		
		//判断机械数据库表是否存在
		String sql_Vcl = "select count(1) as counts from CTY_Vcl.dbo.SysObjects Where XType='U' and Name=?";
		if(isTwo){
			sql_Vcl += " or Name='"+"Vcl_Pstn_"+TableName_New+"'";
		}
		Map<String,Object> map_Vcl = baseDAO.execQueryOne(sql_Vcl, new Object[]{"Vcl_Pstn_"+TableName});
		int counts_Vcl = MapGetter.getInt(map_Vcl, "counts");
		
		//上次统计的最大定时信息MsgT_ID值
		String StatR_StatDataID = MapGetter.getString(StatRecord, "StatR_StatDataID");
		
		//获取源数据
		StringBuffer sql = new StringBuffer();
		if(counts_B>0 && counts_Vcl>0){
			sql.append("select * from ( ");
			sql.append("select MsgT_ID,MsgT_Vcl_ID,MsgT_MsgTime,MsgT_VclSpeed,VclP_Lo,VclP_La,VehicleNumber,DBCode from CTY_Egn_B.dbo.Msg_Timing_"+TableName+" as m ");
			sql.append("inner join CTY_Vcl.dbo.Vcl_Pstn_"+TableName+" as v on MsgT_ID=VclP_MsgID  ");
			sql.append("inner join ( ");
			sql.append("select VclP_MsgID,MAX(VclP_ID) as VclP_ID from CTY_Vcl.dbo.Vcl_Pstn_"+TableName+" ");
			sql.append("inner join V_VehicleInfo on VehicleID=VclP_Vcl_ID and VclP_MsgTp_ID=2 ");
			sql.append("group by VclP_MsgID )tmp on v.VclP_ID=tmp.VclP_ID ");
			sql.append("inner join V_VehicleInfo on m.MsgT_Vcl_ID=VehicleID "); 
			sql.append("where MsgT_SysTI_ID=64 and Tmnl_SoftEdition in ("+EquipVersionCommon.getSoftVerByAgrType("GAVcl_JPP")+") ");
			if(StringUtil.isValid(StatR_StatDataID)){
				sql.append(" and MsgT_ID>"+StatR_StatDataID);
			}
			if(isTwo && counts_B>1 && counts_Vcl>1){
				sql.append("union all ");
				sql.append("select MsgT_ID,MsgT_Vcl_ID,MsgT_MsgTime,MsgT_VclSpeed,VclP_Lo,VclP_La,VehicleNumber,DBCode from CTY_Egn_B.dbo.Msg_Timing_"+TableName_New+" as m ");
				sql.append("inner join CTY_Vcl.dbo.Vcl_Pstn_"+TableName_New+" as v on MsgT_ID=VclP_MsgID "); 
				sql.append("inner join ( ");
				sql.append("select VclP_MsgID,MAX(VclP_ID) as VclP_ID from CTY_Vcl.dbo.Vcl_Pstn_"+TableName_New+" ");
				sql.append("inner join V_VehicleInfo on VehicleID=VclP_Vcl_ID and VclP_MsgTp_ID=2 ");
				sql.append("group by VclP_MsgID )tmp on v.VclP_ID=tmp.VclP_ID ");
				sql.append("inner join V_VehicleInfo on m.MsgT_Vcl_ID=VehicleID "); 
				sql.append("where MsgT_SysTI_ID=64 and Tmnl_SoftEdition in ("+EquipVersionCommon.getSoftVerByAgrType("GAVcl_JPP")+") ");
			}else if(StatRecord == null){	//初次统计，统计距当前前5分钟的数据
				sql.append(" and MsgT_MsgTime > DATEADD(N,-5,GETDATE()) ");
			}
			
			sql.append(") t order by MsgT_MsgTime,MsgT_ID");
		}else{
			return null;
		}
		
		return baseDAO.execQuery(sql.toString(), new Object[]{});
	}
	
	/**
	 * 将提醒数据插入到提示信息表中
	 * @param map
	 * @param state	1：在允许区域超速时开启车门；2：在非允许区域开启车门
	 * @return
	 */
	public boolean insertPrompt(Map<String,Object> map,int type){
		//设备ID
		String MsgT_Vcl_ID = MapGetter.getString(map, "MsgT_Vcl_ID");
		//设备号
		String VehicleNumber = MapGetter.getString(map, "VehicleNumber");
		//信息生成时间
		String MsgT_MsgTime = MapGetter.getString(map, "MsgT_MsgTime");
		//信息ID
		String MsgT_ID = MapGetter.getString(map, "MsgT_ID");
		//提示内容
		String content = VehicleNumber+"在"+DateList.getFormatDate(MsgT_MsgTime, 10)+"检测到门控异常开启报警，请注意";
		//DBCode
		String DBCode = MapGetter.getString(map, "DBCode");
		String MsgT_Lo = MapGetter.getString(map, "VclP_Lo");
		String MsgT_La = MapGetter.getString(map, "VclP_La");
		//发送短信
		List<String> sqlList = new ArrayList<String>();
		List<Object[]> paramList = new ArrayList<Object[]>();
		//获取短信接收人
		List<Map<String,Object>> userList = smsInfoDAO.getSMSUsers("SMSAS_SMSMkbj", DBCode, MsgT_Vcl_ID);
		String recUsers = smsInfoDAO.getSMSUsers(userList);
		if(StringUtil.isNullOrEmpty(recUsers))
			recUsers = "，"+recUsers;
		Map<String, Object> SMSMap = new HashMap<String, Object>();// 短信信息
		SMSMap.put("SMSType", "SMSAS_SMSMkbj");
		SMSMap.put("TabName", "Msg_Prompt");
		SMSMap.put("TabNameShort", "Msg_Timing");
		SMSMap.put("SMSContent", content);
		SMSMap.put("PromptType", SMSPromptType.SMSGatAlarm);
		SMSMap.put("CreateType", "1");
		SMSMap.put("DBName", "B");
		SMSMap.put("SMSCfgType", SMSCfgType.SMSCfgGatAlarm);
		SMSMap.put("DBCode", DBCode);
		
		//向提示信息表中插入状态
		sqlList.add("insert into Msg_Prompt(MsgP_VclID,MsgP_MsgID,MsgP_MsgTime,MsgP_Type,MsgP_DBName,MsgP_TableName,MsgP_UpdateTime,MsgP_CreateTime," +
				"Msgp_SecTypeID,MsgP_DetailMsg,Msgp_SMSRecUser,MsgP_Lo,MsgP_La)"
			+" values(?,?,?,9,'B','Msg_Timing',GETDATE(),GETDATE(),?,?,?,?,?);");
		paramList.add(new Object[]{MsgT_Vcl_ID,MsgT_ID,MsgT_MsgTime,type,content,recUsers,MsgT_Lo,MsgT_La});
		
		smsInfoDAO.loadSMSInfo(sqlList, paramList, SMSMap,userList);
		
		return tranDAO.batch(sqlList, paramList);
	}
	
	/**
	 * 源数据进行偏移处理
	 * @return
	 */
	public List<Map<String,Object>> disposeData(List<Map<String,Object>> SourceData) throws Exception{
		return ExtTrackPoint.getInstance().pointsRevise(SourceData,"VclP_Lo","VclP_La");
	}
	
	/**
	 * 根据设备所属组织机构获取该组织机构下围栏【门控开关】属性为是且围栏为圆形的数据集合
	 * @param Vcl_ID
	 * @return
	 */
	public List<Map<String,Object>> getCrawlData(String Vcl_ID){
		String sql = "select FenceID,FenceName,SpeedValue,CircleR as radius,CircleX as lng,CircleY as lat from Organization_FenceList as f "
			+"inner join Organization_DB as d on f.OrganizationID=d.OrganizationID "
			+"left join V_VehicleInfo on DB=d.OrganizationID  "
			+"where FenceType='Circle' and SupportDoor=1 and f.OrganizationTypeID=1 "
			+"and VehicleID=?";
		return baseDAO.execQuery(sql, new Object[]{Vcl_ID});
	}
	
	/**
	 * 根据设备所属组织机构获取该组织机构下围栏【门控开关】属性为是，矩形数据集合
	 * SXH 查询 设备-机构-围栏关系
	 * @return
	 */
	public List<Map<String,Object>>getFenceList(){
		String sql="select  vv.vehicleid,FenceID,FenceName,fo.FenO_OrgCode,SpeedValue,CircleR as radius,CircleX as lng,CircleY as lat,OrgFen_RectLeftTX,OrgFen_RectLeftTY,OrgFen_RectRightBX,OrgFen_RectRightBY  from Organization_FenceList as f "+ 
				"inner join Fence_Org fo on FenceID=FenO_FenceID "+
				"inner join Organization_DB as d on fo.FenO_OrgCode=d.OrganizationCode "+ 
				"inner join V_VehicleInfo vv on vv.DBCode like fo.FenO_OrgCode+'%' " +
				"where SupportDoor=1  and OrgFen_State=1 and FenceType=3";
		return baseDAO.execQuery(sql, new Object[]{});
	}
	/**
	 * 更新统计标记表
	 * @param map
	 * @return
	 */
	public int updateStatRecord(Map<String,Object> map){
		String StatR_StatDataID = MapGetter.getString(map, "MsgT_ID");
		String StatR_TableData = MapGetter.getString(map, "MsgT_MsgTime");
		String sql = "insert into StatRecord (StatR_StatDataID,StatR_Type,StatR_TableData,StatR_OprtType,StatR_UpdateTime,StatR_MsgTime)values(?,1,?,1,GETDATE(),GETDATE())";
		return baseDAO.execInsert(sql, new Object[]{StatR_StatDataID,StatR_TableData});
	}
	
	/**
	 * 获取统计标记表数据
	 * @param StatR_Type	统计类型
	 * @return
	 */
	public Map<String, Object> getStatDataID(String StatR_Type){
		String sql = "select top 1 StatR_StatDataID,StatR_TableData from StatRecord " +
				"where StatR_Type=? order by StatR_ID desc";
		return  baseDAO.execQueryOne(sql, new Object[]{StatR_Type});
	}

	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}

	public ITransactionDAO getTranDAO() {
		return tranDAO;
	}

	public void setTranDAO(ITransactionDAO tranDAO) {
		this.tranDAO = tranDAO;
	}

	public SMSInfoDAO getSmsInfoDAO() {
		return smsInfoDAO;
	}

	public void setSmsInfoDAO(SMSInfoDAO smsInfoDAO) {
		this.smsInfoDAO = smsInfoDAO;
	}
	/*从提示开关表中获取故障提示开关为关的设备号集合20141016wangkai
	 * 3.6.6新增
	 * vclpromptswitch:提示开关表
	 * VclPS_isOpen=0：开关状态为关
	 * VclPS_Type=3：提示开关类型为门控
	 * */
	public List<String> gatingAutoAlarmPS() {
		List<String> gatingPromSwitch = new ArrayList<String>();
		
		String sql = "select VclPS_VclID from " +GlobalDBVar.getDBFullName_Conn("VclPromptSwitch","1",null)+
				" where VclPS_isOpen=0 and VclPS_Type=3 ";
		List<Map<String,Object>> list = baseDAO.execQuery(sql, new Object[]{});
		for (Map<String, Object> map : list) {
			gatingPromSwitch.add(MapGetter.getString(map, "VclPS_VclID"));
		}
		return gatingPromSwitch;
	}
	public Map<String,String> SelectAllVcl() {
		List<String> gatingPromSwitch = new ArrayList<String>();
		String sql = "select VehicleID,DBCode from V_VehicleInfo";
		List<Map<String,Object>> list = baseDAO.execQuery(sql, new Object[]{});
		Map<String,String> maps=new HashMap<String,String>();
		for (Map<String, Object> map : list) {
			String vcl=map.get("VehicleID").toString();
			String orgCode=map.get("DBCode").toString();
			maps.put(vcl,orgCode);
		}
		return maps;
	}
}
